package com.liangtian.mdm.base.dao;

import com.liangtian.mdm.base.constants.ResultCode;
import com.liangtian.mdm.base.exception.BaseRuntimeException;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 使用数据源得到的jdbc连接
 *
 * @author honye
 */
@Slf4j
@Repository
public class JdbcDao {

    @Autowired
    private DataSource dataSource;

    /**
     * 查询以序列生成的一串ID值
     *
     * @param seqName 序列名
     * @param counts  ID数量
     * @return
     * @throws SQLException
     */
    public String[] getIdsBySeq(String seqName, int counts) throws SQLException {

        Connection conn = null;
        CallableStatement stmt = null;
        try {
            //conn = dataSource.getConnection();
            conn = DataSourceUtils.getConnection(dataSource);
            stmt = conn.prepareCall("{call get_ids_by_seq(?,?,?)}");
            stmt.setString(1, seqName);
            stmt.setInt(2, counts);
            stmt.registerOutParameter(3, Types.VARCHAR);
            stmt.execute();
            String ret = stmt.getString(3);
            log.info("-------sequence value-------" + ret);
            String[] ids = ret.split(",");
            if (ids.length != counts)
                throw new RuntimeException("");
            return ids;
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            if (stmt != null)
                stmt.close();
            if (conn != null)
                DataSourceUtils.doReleaseConnection(conn, dataSource);
        }
    }

    /**
     * @param seqName 序列名
     * @param length  值长度
     * @return
     * @throws SQLException
     * @author honye 2016-10-24
     * 判断给定序列是否存在，并获取给定长度的序列值 （用户自定义配码）
     */
    public String genSeqGetVal(String seqName, int length) throws SQLException {

        Connection conn = null;
        CallableStatement stmt = null;
        try {
            //conn = dataSource.getConnection();
            conn = DataSourceUtils.getConnection(dataSource);
            stmt = conn.prepareCall("{call gen_seq_get_val(?,?,?)}");
            stmt.setString(1, seqName);
            stmt.setInt(2, length);
            stmt.registerOutParameter(3, Types.VARCHAR);
            stmt.execute();
            String ret = stmt.getString(3);
            log.info("-------sequence value-------" + ret);
            return ret;
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            if (stmt != null)
                stmt.close();
            if (conn != null)
                DataSourceUtils.doReleaseConnection(conn, dataSource);
        }
    }

    /**
     * 批量执行ddl
     *
     * @param sqlList
     * @param autoCommit 是否独立事务
     * @return
     */
    public boolean executeSqlList(List<String> sqlList, boolean autoCommit) {
        boolean r = true;
        Connection conn = null;
        Statement stmt = null;
        try {
            //connection = dataSource.getConnection();
            if (sqlList.size() > 0) {
                conn = DataSourceUtils.getConnection(dataSource);
                if (autoCommit) {
                    conn.setAutoCommit(false);
                }
                stmt = conn.createStatement();
                log.info("执行的批量sql为：{}" , sqlList);
                for (String sql : sqlList) {
                    if (sql == null || "".equals(sql.trim())) {
                        continue;
                    }
                    log.info("执行的sql：{}",sql);
                    stmt.addBatch(sql);
                }
                //conn.setAutoCommit(false);
                stmt.executeBatch();
                if (autoCommit) {
                    conn.commit();
                }
            }
        } catch (Exception e) {
            r = false;
            try {
                if (autoCommit) {
                    conn.rollback();
                }
            } catch (SQLException e1) {
                throw new BaseRuntimeException(ResultCode.FAIL_EXECUTE_SQL.getCode(),"执行批量sql回滚出现异常", e);
            }
            e.printStackTrace();
            throw new BaseRuntimeException(ResultCode.FAIL_EXECUTE_SQL.getCode(),"执行批量sql出现异常", e);
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    DataSourceUtils.doReleaseConnection(conn, dataSource);
                }
            } catch (SQLException e) {
                throw new BaseRuntimeException(ResultCode.FAIL_EXECUTE_SQL.getCode(),"释放资源出现异常", e);
            }
        }
        return r;
    }

    public boolean executeSql(String sql) {
        boolean r = true;
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = DataSourceUtils.getConnection(dataSource);
            stmt = conn.createStatement();
            log.info("执行的sql为：" + sql);
            // true if the first result is a ResultSet object; false if it is an update count or there are no results
            // r = stmt.execute(sql);
            conn.setAutoCommit(false);
            stmt.executeUpdate(sql);
            conn.commit();
        } catch (Exception e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            throw new RuntimeException("执行sql出现异常", e);
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
                if (conn != null)
                    DataSourceUtils.doReleaseConnection(conn, dataSource);
            } catch (SQLException e) {
                throw new RuntimeException("释放资源出现异常", e);
            }
        }
        return r;
    }

    public List<String> executeSqlSelectId(String sql) {
        boolean r = true;
        Connection conn = null;
        Statement stmt = null;
        List<String> idList = new ArrayList<String>();
        try {
            conn = DataSourceUtils.getConnection(dataSource);
            stmt = conn.createStatement();
            log.info("执行的sql为：" + sql);
            // true if the first result is a ResultSet object; false if it is an update count or there are no results
            // r = stmt.execute(sql);
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                String id = rs.getString(1);
                idList.add(id);
            }
            conn.commit();
        } catch (Exception e) {
            throw new RuntimeException("执行sql出现异常", e);
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
                if (conn != null)
                    DataSourceUtils.doReleaseConnection(conn, dataSource);
            } catch (SQLException e) {
                throw new RuntimeException("释放资源出现异常", e);
            }
        }
        return idList;
    }

    //用来查询传递过来的数据库表是否存在---for:外部取值型的组建要用来校验所填写的外部(非动态模型)数据库表是否存在
    public boolean executeSysSql(String tableName) {
        boolean b = false;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
//		String sql = "select count(1) from tabs where table_name ='"+tableName+"'";

        String sql = "SELECT count(1) FROM all_tab_cols WHERE TABLE_NAME = '" + tableName + "'";
        try {
            conn = DataSourceUtils.getConnection(dataSource);
            stmt = conn.createStatement();
            log.info("执行的sql为：" + sql);
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                b = (0 == rs.getShort(1) ? false : true);
            }
        } catch (SQLException e) {
            throw new RuntimeException("执行sql出现异常", e);
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (stmt != null)
                    stmt.close();
                if (conn != null)
                    DataSourceUtils.doReleaseConnection(conn, dataSource);
            } catch (SQLException e) {
                throw new RuntimeException("释放资源出现异常", e);
            }
        }
        return b;
    }

    //用来查询传递过来的数据库表的comments---for:外部取值型的组建要用,用来获取外部(非动态模型)数据库表的列名和注释,用来展示给用户,供用户选择取值的具体字段
    public Map<String, String> executeSysSqlForComments(String tableName) {
        Map<String, String> map = new HashMap<String, String>();// = false;
        String sql = "select column_name code, comments name from user_col_comments where Table_Name = '" + tableName + "'";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = DataSourceUtils.getConnection(dataSource);
            stmt = conn.createStatement();
            log.info("执行的sql为：" + sql);

            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                map.put(rs.getString("code"), rs.getString("name"));
            }
        } catch (SQLException e) {
            throw new RuntimeException("执行sql出现异常", e);
        } finally {
            try {
                if (null != rs)
                    rs.close();
                if (null != stmt)
                    stmt.close();
                if (null != conn)
                    DataSourceUtils.doReleaseConnection(conn, dataSource);
            } catch (SQLException e) {
                throw new RuntimeException("释放资源出现异常", e);
            }
        }
        return map;
    }

    //查询数量
    public int executeSqlForCount(String sql) {
        int count = 0;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = DataSourceUtils.getConnection(dataSource);
            stmt = conn.createStatement();
            log.info("执行的sql为：" + sql);

            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException("执行sql出现异常", e);
        } finally {
            try {
                if (null != rs)
                    rs.close();
                if (null != stmt)
                    stmt.close();
                if (null != conn)
                    DataSourceUtils.doReleaseConnection(conn, dataSource);
            } catch (SQLException e) {
                throw new RuntimeException("释放资源出现异常", e);
            }
        }
        return count;
    }

    public String executeSqlSelectIdNoList(String sql) {
        Connection conn = null;
        Statement stmt = null;
        String id = "";
        try {
            conn = DataSourceUtils.getConnection(dataSource);
            stmt = conn.createStatement();
            log.info("执行的sql为：" + sql);
            // true if the first result is a ResultSet object; false if it is an update count or there are no results
            // r = stmt.execute(sql);
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                id = rs.getString(1);
            }
            conn.commit();
        } catch (Exception e) {
            throw new RuntimeException("执行sql出现异常", e);
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
                if (conn != null)
                    DataSourceUtils.doReleaseConnection(conn, dataSource);
            } catch (SQLException e) {
                throw new RuntimeException("释放资源出现异常", e);
            }
        }
        return id;
    }


}
